package com.telsoft.hms.data;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map.Entry;

import com.telsoft.hms.entity.ETT_Title;
import com.telsoft.hms.lib.admin.client.data.ClientDataPreprocessor;
import com.telsoft.hms.util.SqlUtil;
/**
 * 
 * @author VIETPH
 *
 */
public class HMS_Title_DATA extends ClientDataPreprocessor {

	public HMS_Title_DATA() {
		super();
	}

	/**
	 * Lấy ra danh sách chức danh theo ngôn ngữ
	 * 
	 * @param languageCode
	 *            Mã ngôn ngữ
	 * @param hospitalKey
	 *            Mã bệnh viện
	 * @return
	 * @throws Exception
	 */
	public List<ETT_Title> getListTitle(int languageCode, String hospitalKey)
			throws Exception {
		List<ETT_Title> listTitle = new ArrayList<ETT_Title>();
		String sql = "SELECT t.TITLE_ID, t.LANGUAGE_CODE, t.SHORT_DISPLAY, "
				+ "t.DISPLAY_VALUE, t.DESCRIPTION, "
				+ "tr.IS_DEFAULT, tr.IS_INTERNAL, tr.STATUS "
				+ "FROM TITLE_VALUES t JOIN TITLE_REF tr ON t.TITLE_ID = tr.TITLE_ID "
				+ "WHERE LANGUAGE_CODE = ?";
		try {
			open(hospitalKey);
			mStmt = mConnection.prepareStatement(sql);
			mStmt.setInt(1, languageCode);
			mRs = mStmt.executeQuery();
			while (mRs.next()) {
				ETT_Title title = new ETT_Title();
				title.setTitleId(mRs.getLong("TITLE_ID"));
				title.setIsdefault(mRs.getString("IS_DEFAULT"));
				title.setIsinternal(mRs.getString("IS_INTERNAL"));
				title.setStatus(mRs.getString("STATUS"));
				title.setLanguageCode(mRs.getInt("LANGUAGE_CODE"));
				title.setShortDisplay(mRs.getString("SHORT_DISPLAY"));
				title.setDisplayValue(mRs.getString("DISPLAY_VALUE"));
				title.setDescription(mRs.getString("DESCRIPTION"));

				listTitle.add(title);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			close(mConnection, mStmt, mRs);
		}
		return listTitle;
	}

	/**
	 * Thêm hoặc cập nhật một chức danh theo ngôn ngữ
	 * 
	 * @param hospitalKey
	 *            Mã bệnh viện
	 * @param title
	 *            thực thể chức danh cần thêm hoặc cập nhật
	 * @param languageCode
	 * @return Id chức danh vừa thêm hoặc cập nhật
	 * @throws Exception
	 */
	public long addOrUpdateTitle(String hospitalKey, ETT_Title title,
			int languageCode) throws Exception {
		if (title.getTitleId() == 0) {
			return addTitle(hospitalKey, title, languageCode);
		} else {
			return updateTitle(hospitalKey, title, languageCode);
		}
	}

	/**
	 * Xóa một chức danh theo ngôn ngữ
	 * 
	 * @param hospitalKey
	 *            Mã bệnh viện
	 * @param lTitleId
	 *            ID chức danh
	 * @param languageCode
	 *            Mã ngôn ngữ
	 * @throws Exception
	 */
	public void deleteTitle(String hospitalKey, long[] lTitleId,
			int languageCode) throws Exception {
		try {
			open(hospitalKey);
			mConnection.setAutoCommit(false);

			// query xóa bảng TITLE_REF
			StringBuilder sbTitleCmd = new StringBuilder(
					"DELETE FROM TITLE_REF WHERE TITLE_ID IN (");

			// query xóa bảng TITLE_VALUES
			StringBuilder sbTitleLgCmd = new StringBuilder(
					"DELETE FROM TITLE_VALUES WHERE TITLE_ID IN (");

			// Danh sách TitleID cần xóa
			List<Long> lDeleteTitle = new ArrayList<>();

			// Danh sách TitleID ngôn ngữ tương ứng cần xóa
			List<Long> lDeleteLanguage = new ArrayList<>();

			HashMap<Long, Integer> listCountLanguage = getLanguageCount(lTitleId);

			// Lọc chức danh có 1 ngôn ngữ
			for (Entry<Long, Integer> entry : listCountLanguage.entrySet()) {
				if (entry.getValue() == 1) {
					lDeleteTitle.add(entry.getKey());
				}
				lDeleteLanguage.add(entry.getKey());
			}

			for (int i = 0; i < lDeleteTitle.size(); i++) {
				if (i == lDeleteTitle.size() - 1) {
					sbTitleCmd.append(lDeleteTitle.get(i)).append(") ");
				} else {
					sbTitleCmd.append(lDeleteTitle.get(i)).append(", ");
				}
			}

			for (int i = 0; i < lDeleteLanguage.size(); i++) {
				if (i == lDeleteLanguage.size() - 1) {
					sbTitleLgCmd.append(lDeleteLanguage.get(i)).append(") ");
				} else {
					sbTitleLgCmd.append(lDeleteLanguage.get(i)).append(", ");
				}
			}
			sbTitleLgCmd.append("AND LANGUAGE_CODE = ?");

			if (lDeleteLanguage.size() > 0) {
				mStmt = mConnection.prepareStatement(sbTitleLgCmd.toString());
				mStmt.setInt(1, languageCode);
				mStmt.executeUpdate();
				mStmt.clearBatch();
			}

			if (lDeleteTitle.size() > 0) {
				mStmt = mConnection.prepareStatement(sbTitleCmd.toString());
				mStmt.executeUpdate();
				mStmt.clearBatch();
			}

			mConnection.commit();
		} catch (Exception ex) {
			mConnection.rollback();
			throw ex;
		} finally {
			mConnection.setAutoCommit(false);
			close(mStmt, mConnection);
		}
	}

	/**
	 * Lấy ra danh sách ngôn ngữ khác của chức danh tương ứng
	 * 
	 * @param hospitalKey
	 *            Mã bệnh viện
	 * @param titleId
	 *            Id chức danh
	 * @param languageCode
	 *            Mã ngôn ngữ
	 * @return
	 * @throws Exception
	 */
	public List<ETT_Title> getListTitleLanguage(String hospitalKey,
			long titleId, int languageCode) throws Exception {
		List<ETT_Title> listTitle = new ArrayList<ETT_Title>();
		String sql = "SELECT tv.TITLE_ID, tv.LANGUAGE_CODE, tv.SHORT_DISPLAY, tv.DISPLAY_VALUE, tv.DESCRIPTION, lg.DISPLAY_VALUE AS \"LANGUAGE_DISPLAY\", lg.SHORT_DISPLAY AS \"LANGUAGE_SHORT_DISPLAY\" "
				+ "FROM TITLE_VALUES tv JOIN LANGUAGE_CODE_VALUES lg ON tv.LANGUAGE_CODE = lg.INPUT_LANGUAGE "
				+ "WHERE tv.TITLE_ID = ?"
				+ " AND tv.LANGUAGE_CODE != ?";
		try {
			open(hospitalKey);
			mStmt = mConnection.prepareStatement(sql);
			mStmt.setLong(1, titleId);
			mStmt.setInt(2, languageCode);
			mRs = mStmt.executeQuery();
			while (mRs.next()) {
				ETT_Title title = new ETT_Title();
				title.setTitleId(mRs.getLong("TITLE_ID"));
				title.setLanguageCode(mRs.getInt("LANGUAGE_CODE"));
				title.setShortDisplay(mRs.getString("SHORT_DISPLAY"));
				title.setDisplayValue(mRs.getString("DISPLAY_VALUE"));
				title.setDescription(mRs.getString("DESCRIPTION"));
				title.setLanguageDisplayValue("LANGUAGE_DISPLAY");
				title.setLanguageShortDisplay("LANGUAGE_SHORT_DISPLAY");
				listTitle.add(title);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			close(mConnection, mStmt, mRs);
		}
		return listTitle;
	}

	/**
	 * Thêm mới một ngôn ngữ cho chức danh tương ứng
	 * 
	 * @param hospitalKey
	 *            Mã bệnh viện
	 * @param title
	 *            Thực thể ngôn ngữ chức danh
	 * @return Id chức danh vừa thêm ngôn ngữ
	 * @throws Exception
	 */
	public long addTitleLanguage(String hospitalKey, ETT_Title title)
			throws Exception {
		String sCommand = "INSERT INTO TITLE_VALUES(TITLE_ID, LANGUAGE_CODE, SHORT_DISPLAY, DISPLAY_VALUE, DESCRIPTION) VALUES(?, ?, ?, ?, ?)";
		try {
			open(hospitalKey);
			mStmt = mConnection.prepareStatement(sCommand);
			mStmt.setLong(1, title.getTitleId());
			mStmt.setInt(2, title.getLanguageCode());
			mStmt.setString(3, title.getShortDisplay());
			mStmt.setString(4, title.getDisplayValue());
			mStmt.setString(5, title.getDescription());

			mStmt.executeUpdate();
		} finally {
			close(mStmt, mConnection);
		}
		return title.getTitleId();
	}

	/**
	 * Cập nhật một ngôn ngữ cho chức danh tương ứng
	 * 
	 * @param hospitalKey
	 *            Mã bệnh viện
	 * @param title
	 *            Thực thể ngôn ngữ chức danh
	 * @return Id chức danh vừa cập nhật ngôn ngữ
	 * @throws Exception
	 */
	public long updateTitleLanguage(String hospitalKey, ETT_Title title)
			throws Exception {
		String sCommand = "UPDATE TITLE_VALUES SET SHORT_DISPLAY = ?, DISPLAY_VALUE = ?, DESCRIPTION = ? WHERE TITLE_ID = ? AND LANGUAGE_CODE=?";
		try {
			open(hospitalKey);
			mStmt = mConnection.prepareStatement(sCommand);
			mStmt.setLong(4, title.getTitleId());
			mStmt.setInt(5, title.getLanguageCode());
			mStmt.setString(1, title.getShortDisplay());
			mStmt.setString(2, title.getDisplayValue());
			mStmt.setString(3, title.getDescription());

			mStmt.executeUpdate();
		} finally {
			close(mStmt, mConnection);
		}
		return title.getTitleId();
	}

	/**
	 * Xóa một ngôn ngữ cho chức danh tương ứng
	 * 
	 * @param hospitalKey
	 *            Mã bệnh viện
	 * @param titleId
	 *            Id chức danh cần xóa
	 * @param lLanguageCode
	 *            Mảng danh sách ngôn ngữ cần xóa theo Id chức danh tương ứng
	 * @throws Exception
	 */
	public void deleteTitleLanguage(String hospitalKey, long titleId,
			int[] lLanguageCode) throws Exception {
		StringBuilder sbTitleLgCmd = new StringBuilder(
				"DELETE FROM TITLE_VALUES WHERE LANGUAGE_CODE IN (");
		for (int i = 0; i < lLanguageCode.length; i++) {
			if (i == lLanguageCode.length - 1) {
				sbTitleLgCmd.append(lLanguageCode[i]).append(") ");
			} else {
				sbTitleLgCmd.append(lLanguageCode[i]).append(", ");
			}
		}
		sbTitleLgCmd.append("AND TITLE_ID = ?");
		try {
			open(hospitalKey);
			mStmt = mConnection.prepareStatement(sbTitleLgCmd.toString());
			mStmt.setLong(1, titleId);
			mStmt.executeUpdate();
		} finally {
			close(mStmt, mConnection);
		}
	}

	/**
	 * Lấy ra danh sách số lượng ngôn ngữ của chức danh tương ứng
	 * 
	 * @param lTitleId
	 *            ID chức danh
	 * @return
	 * @throws Exception
	 */
	private HashMap<Long, Integer> getLanguageCount(long[] lTitleId)
			throws Exception {
		HashMap<Long, Integer> listCountLanguage = new HashMap<>();
		String sql = "SELECT TITLE_ID, COUNT(TITLE_ID) AS \"COUNT\" FROM TITLE_VALUES WHERE TITLE_ID IN (";
		StringBuilder sb = new StringBuilder(sql);

		for (int i = 0; i < lTitleId.length; i++) {
			if (i == lTitleId.length - 1) {
				sb.append(lTitleId[i]).append(") ");
			} else {
				sb.append(lTitleId[i]).append(", ");
			}
		}
		sb.append("GROUP BY TITLE_ID");

		mStmt = mConnection.prepareStatement(sb.toString());
		mRs = mStmt.executeQuery();

		while (mRs.next()) {
			listCountLanguage.put(mRs.getLong("TITLE_ID"), mRs.getInt("COUNT"));
		}
		return listCountLanguage;
	}

	private long addTitle(String hospitalKey, ETT_Title title, int languageCode)
			throws Exception {
		long titleId = 0;
		try {
			open(hospitalKey);
			mConnection.setAutoCommit(false);
			titleId = Long.parseLong(SqlUtil.getSequenceValue(mConnection,
					"TITLE_SEQ"));
			String sTitleCmd = "INSERT INTO TITLE_REF(TITLE_ID, IS_DEFAULT, IS_INTERNAL, STATUS) VALUES(?, ?, ?, ?)";
			String sLanguageCmd = "INSERT INTO TITLE_VALUES(TITLE_ID, LANGUAGE_CODE, SHORT_DISPLAY, DISPLAY_VALUE, DESCRIPTION) VALUES(?, ?, ?, ?, ?)";

			mStmt = mConnection.prepareStatement(sTitleCmd);
			mStmt.setLong(1, titleId);
			mStmt.setString(2, title.getIsdefault());
			mStmt.setString(3, title.getIsinternal());
			mStmt.setString(4, title.getStatus());
			mStmt.executeUpdate();
			mStmt.clearBatch();

			mStmt = mConnection.prepareStatement(sLanguageCmd);
			mStmt.setLong(1, titleId);
			mStmt.setInt(2, languageCode);
			mStmt.setString(3, title.getShortDisplay());
			mStmt.setString(4, title.getDisplayValue());
			mStmt.setString(5, title.getDescription());

			mStmt.executeUpdate();
			mStmt.clearBatch();
			mConnection.commit();
		} catch (Exception ex) {
			mConnection.rollback();
			throw ex;
		} finally {
			close(mStmt, mConnection);
		}
		return titleId;
	}

	private long updateTitle(String hospitalKey, ETT_Title title,
			int languageCode) throws Exception {
		try {
			open(hospitalKey);
			String sTitleCmd = "UPDATE TITLE_REF SET IS_DEFAULT = ?, IS_INTERNAL = ?, STATUS = ? WHERE TITLE_ID = ?";
			String sLanguageCmd = "UPDATE TITLE_VALUES SET SHORT_DISPLAY = ?, DISPLAY_VALUE = ?, DESCRIPTION = ? WHERE TITLE_ID = ? AND LANGUAGE_CODE = ?";

			mStmt = mConnection.prepareStatement(sTitleCmd);

			mStmt.setString(1, title.getIsdefault());
			mStmt.setString(2, title.getIsinternal());
			mStmt.setString(3, title.getStatus());
			mStmt.setLong(4, title.getTitleId());

			mStmt.addBatch(sLanguageCmd);
			mStmt.setString(1, title.getShortDisplay());
			mStmt.setString(2, title.getDisplayValue());
			mStmt.setString(3, title.getDescription());
			mStmt.setLong(4, title.getTitleId());
			mStmt.setInt(5, languageCode);

			mStmt.executeBatch();
			mStmt.clearBatch();
			mConnection.commit();

		} catch (Exception e) {
			mConnection.rollback();
			throw new Exception(e);
		} finally {
			mConnection.setAutoCommit(false);
			close(mStmt, mConnection);
		}
		return title.getTitleId();
	}
}
